#!/usr/bin/perl

use strict;
use DBI;
use CGI;

# log_entries.pl
#  - Jamie Stevens 2010-08-07

# This script is a collection of subroutines for manipulating online
# log entries.

my $in=CGI->new;

my %input=$in->Vars;

# the database handle
my $dbh;

my $action=$input{"action"};

if ($action eq "new"){
    # make a new entry
    my $log_id=$input{"log_id"};

    &start_database_connection();
    
    my ($new_entry_id,$new_entry_text,$entry_time)=&new_entry($log_id);
    # change the entry time to exclude subseconds
    $entry_time=~s/^(.*)\..*$/$1/;

    # print out the information in the new entry
    print $in->header('text/html');
    print $new_entry_id."|".$new_entry_text."|".
	$entry_time;

    $dbh->disconnect;
} elsif ($action eq "start_modify"){
    # mark an entry as being edited
    my $entry_id=$input{"entry_id"};

    &start_database_connection();

    my $rc=&modify_entry_start($entry_id);
    # $rc will be 0 now if the entry has not already been marked
    # as being edited before our request, or 1 if it has

    print $in->header('text/html');
    print $rc;

    $dbh->disconnect;
} elsif ($action eq "end_modify"){
    # update an edited entry
    my $entry_id=$input{"entry_id"};
    my $entry_type=$input{"entry_type"};
    my $entry_value=$input{"entry_value"};

    &start_database_connection;

    print $in->header('text/html');
    my ($new_entry)=&modify_entry_end($entry_id,$entry_type,
				      $entry_value);
    print $new_entry;

    $dbh->disconnect;
} elsif ($action eq "get_all"){
    # return all the entries associated with this log
    my $log_id=$input{"log_id"};

    &start_database_connection;

    print $in->header('text/html');
    &get_all_entries($log_id);

    $dbh->disconnect;
}

exit;

sub get_all_entries {
    my ($log_id)=@_;

    my $all_entries_query="SELECT entry_id,time,log_entry,user_editable,".
	"entry_type FROM entry WHERE log_id=".$log_id." AND deleted=FALSE ".
	"ORDER BY time";
    my $all_entries=$dbh->prepare($all_entries_query);
    $all_entries->execute;
    while (my @entries_response=$all_entries->fetchrow){
	for (my $i=0;$i<=$#entries_response;$i++){
	    if ($i>0){
		print "|";
	    }
	    if ($i==1){
		# we exclude the subseconds from the time string
		$entries_response[$i]=~s/^(.*)\..*/$1/;
	    }
	    # replace the newlines with HTML breaks
	    $entries_response[$i]=~s/\n/\<br \/\>/g;
	    print $entries_response[$i];
	}
	print "\n";
    }
    $all_entries->finish;
}

sub modify_entry_end {
    my ($entry_id,$entry_type,$entry_value)=@_;

    # set the entry as not being updated. we do this separately
    # first, since if the value update query (which happens next)
    # fails, the editing flag still gets unset
    my $update_entry_edit_query="UPDATE entry SET (being_edited,".
	"edit_time)=(false,NULL) WHERE entry_id=".$entry_id;
    my $update_entry_edit=$dbh->prepare($update_entry_edit_query);
    $update_entry_edit->execute;
    $update_entry_edit->finish;
    
    my $update_entry_query="UPDATE entry SET ";
    if ($entry_type eq "time"){
	$update_entry_query.="time";
    } elsif ($entry_type eq "entry"){
	$update_entry_query.="log_entry";
    }
    $entry_value=~s/\'/\\'/g;
    $update_entry_query.=" = ".
	"'".$entry_value."' WHERE entry_id=".$entry_id;
    my $update_entry=$dbh->prepare($update_entry_query);
    $update_entry->execute;
    $update_entry->finish;

    # now grab the finished entries
    my $finished_entry_query="SELECT ";
    if ($entry_type eq "time"){
	$finished_entry_query.="time";
    } elsif ($entry_type eq "entry"){
	$finished_entry_query.="log_entry";
    }
    $finished_entry_query.=" FROM entry WHERE entry_id=".$entry_id;
    my $finished_entry=$dbh->prepare($finished_entry_query);
    $finished_entry->execute;
    my ($entry_finished)=$finished_entry->fetchrow;
    $finished_entry->finish;

    # edit the time if necessary to exclude subseconds
    if ($entry_type eq "time"){
	$entry_finished=~s/^(.*)\..*/$1/;
    }
    
    return $entry_finished;
}

sub modify_entry_start {
    my ($entry_id)=@_;

    # check if this entry is already being modified
    my $check_modification_query="SELECT being_edited FROM entry ".
	"WHERE entry_id=".$entry_id;
    my $check_modification=$dbh->prepare($check_modification_query);
    $check_modification->execute;
    my ($is_edited)=$check_modification->fetchrow;
    $check_modification->finish;

    if (!$is_edited){
	my $modify_entry_query="UPDATE entry SET (being_edited,".
	    "edit_time)=(true,timezone('UTC'::text,now())) WHERE ".
	    "entry_id=".$entry_id;
	my $modify_entry=$dbh->prepare($modify_entry_query);
	$modify_entry->execute;
	$modify_entry->finish;
    }

    return $is_edited;
}

sub new_entry {
    my ($log_id)=@_;

    # We make a new entry in the database
    my $new_entry_text="click to edit";
    my $new_entry_query="INSERT INTO entry (log_id,log_entry,user_editable) ".
	"VALUES (".$log_id.",'".$new_entry_text."',TRUE)";
    my $new_entry_insert=$dbh->prepare($new_entry_query);
    $new_entry_insert->execute;
    my $new_entry_id=$dbh->last_insert_id(undef,undef,"entry",undef);
    $new_entry_insert->finish;

    # get the entry time
    my $entry_time_query="SELECT time FROM entry WHERE entry_id=".
	$new_entry_id;
    my $get_entry_time=$dbh->prepare($entry_time_query);
    $get_entry_time->execute;
    my ($entry_time)=$get_entry_time->fetchrow;
    $get_entry_time->finish;

    return ($new_entry_id,$new_entry_text,$entry_time);
}

sub start_database_connection {
    # Here we connect to the database as a user that can update the
    # entry table.

    my $dsn="dbi:Pg:dbname=online_log;host=localhost;port=5432";
    my $db_user="entryuser";
    my $db_password="entryuser";
    $dbh=DBI->connect($dsn,$db_user,$db_password,
		      {RaiseError => 0, AutoCommit => 1,
		       PrintError => 1});
}
